package com.my.zl.console.controller.online;


import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;

import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.util.JdbcUtils;
import com.baomidou.dynamic.datasource.toolkit.DynamicDataSourceContextHolder;
import com.my.zl.common.dto.ReqDTO;
import com.my.zl.common.dto.ResDTO;
import com.my.zl.common.enums.resp.GlobalEnum;
import com.my.zl.common.exception.BizException;
import com.my.zl.console.common.aop.NoLogAnnotation;
import com.my.zl.console.controller.base.BaseController;
import com.my.zl.console.dto.online.databaseTable.ReqDatabaseTableAddDTO;
import com.my.zl.console.dto.online.databaseTable.ReqDatabaseTableQueryDTO;
import com.my.zl.console.dto.online.databaseTable.ReqDatabaseTableSqlQueryDTO;
import com.my.zl.console.dto.online.databaseTable.ResDatabaseTableAddDTO;
import com.my.zl.console.dto.online.databaseTable.ResDatabaseTableQueryDTO;
import com.my.zl.console.dto.online.databaseTable.ResDatabaseTableSqlQueryDTO;
import com.my.zl.console.entity.online.ZlDatabaseInfo;
import com.my.zl.console.service.online.IDatabaseInfoService;
import com.my.zl.console.service.online.IDatabaseTableService;

import io.swagger.annotations.Api;

/**
 * 数据库表字段
 * @author ZL
 */
@Api(value="数据库表字段",tags ="数据库表字段")
@Controller
@RequestMapping("/api/online/databaseTable")
public class DatabaseTableController extends BaseController {

    @Autowired
    private IDatabaseTableService databaseTableService;
    @Autowired
    private IDatabaseInfoService databaseInfoService;
    
    /**
     * 查询表字段
     */
    @NoLogAnnotation
    @ResponseBody
    @RequestMapping(value="/list", method = RequestMethod.GET)
    public ResDTO<ResDatabaseTableQueryDTO> getDatabaseTableList(ReqDatabaseTableQueryDTO reqInfo) throws Exception{
        ResDatabaseTableQueryDTO data = databaseTableService.getDatabaseTableList(reqInfo);
        return successDATA(data);
    }

    /**
     * 获取数据库表
     */
    @NoLogAnnotation
    @ResponseBody
    @RequestMapping(value="/getTableCodes", method = RequestMethod.POST)
    public ResDTO<ResDatabaseTableAddDTO> loadDatabaseTable(@RequestBody ReqDTO<ReqDatabaseTableAddDTO> reqInfo) throws Exception{
        ReqDatabaseTableAddDTO reqData = reqInfo.getReqData();
        
        if(StringUtils.isBlank(reqData.getDatabaseId())){
            throw new BizException(GlobalEnum.DATABASE_CONNECT_FAILED);
        }
        ResDatabaseTableAddDTO  data = new ResDatabaseTableAddDTO();
        try{
            data = databaseTableService.selectDatabaseTableCodes(reqInfo);
            data.getTableColumns().clear();
        }finally {
            DynamicDataSourceContextHolder.clear();
        }
        
        return successDATA(data);
    }
    
    /**
     * 数据库表字段同步
     */
    @NoLogAnnotation
    @ResponseBody
    @RequestMapping(value="/refreshTable", method = RequestMethod.POST)
    public ResDTO<ResDatabaseTableAddDTO> doRefreshDatabaseTable(@RequestBody ReqDTO<ReqDatabaseTableAddDTO> reqInfo) throws Exception{
        ReqDatabaseTableAddDTO reqData = reqInfo.getReqData();
        
        if(StringUtils.isBlank(reqData.getDatabaseId())){
            throw new BizException(GlobalEnum.DATABASE_CONNECT_FAILED);
        }
        
        ResDatabaseTableAddDTO data = databaseTableService.selectDatabaseTableCodes(reqInfo);
        try{
            if(data.getTableColumns()!=null && !data.getTableColumns().isEmpty()){
                if(reqData.getTableCodes()==null || reqData.getTableCodes().size() ==0){
                    databaseTableService.doUpdateDatabaseTableColumnAsync(reqInfo,data);//异步
                }else{
                    databaseTableService.doUpdateDatabaseTableColumn(reqInfo,data);
                }
            }
        }finally {
            DynamicDataSourceContextHolder.clear();
        }
        
        return successDATA(data);
    }
    
    /**
     * 在线SQL查询
     */
    @RequestMapping(value="/sqlQuery", method = {RequestMethod.POST})
    @ResponseBody
    @NoLogAnnotation
    public ResDTO<ResDatabaseTableSqlQueryDTO> querySqlResult(@RequestBody ReqDTO<ReqDatabaseTableSqlQueryDTO> reqInfo) throws Exception{
        ReqDatabaseTableSqlQueryDTO reqData = reqInfo.getReqData();
        
        Pattern RULE = Pattern.compile("\\b(exec|execute|create|insert|delete|update|drop|chr|mid|master|truncate|" +
                "char|declare|sitename|net user|xp_cmdshell|grant|use|group_concat|column_name|" +
                "information_schema.columns|table_schema|chr|mid|master|truncate|--|//|/)\\b",Pattern.CASE_INSENSITIVE);
        
        if(StringUtils.isBlank(reqData.getSqlStr())){
            throw new BizException(GlobalEnum.DATABASE_CONNECT_FAILED);
        }
        
        Matcher sqlm = RULE.matcher(reqData.getSqlStr());
        if(sqlm.find()){
            throw new BizException(GlobalEnum.DATABASE_CONNECT_FAILED);
        }
        
        ResDatabaseTableSqlQueryDTO resDTO = databaseTableService.querySqlResult(reqInfo);
        return successDATA(resDTO);
    }
    
    /**
     * 格式化SQL语句
     */
    @NoLogAnnotation
    @ResponseBody
    @RequestMapping(value="/sqlFormat", method = RequestMethod.POST)
    public ResDTO<ResDatabaseTableSqlQueryDTO> sqlFormat(@RequestBody ReqDTO<ReqDatabaseTableSqlQueryDTO> reqInfo) throws Exception{
        ReqDatabaseTableSqlQueryDTO reqData = reqInfo.getReqData();
        
        ResDatabaseTableSqlQueryDTO resDTO = new ResDatabaseTableSqlQueryDTO();
        
        ZlDatabaseInfo databaseInfo = databaseInfoService.selectDatabaseInfo(reqData.getDatabaseId());
        String databaseType = databaseInfo.getDatabaseType();
        String sourceString = reqData.getSqlStr();
        if(StringUtils.isNotBlank(sourceString)){
            if(databaseType.equalsIgnoreCase(JdbcUtils.ORACLE)){
                sourceString = SQLUtils.format(sourceString,JdbcUtils.ORACLE);
            }else if(databaseType.equalsIgnoreCase(JdbcUtils.MYSQL)){
                sourceString = SQLUtils.format(sourceString,JdbcUtils.MYSQL);
            }else if(databaseType.equalsIgnoreCase(JdbcUtils.DM)){
                sourceString = SQLUtils.format(sourceString,JdbcUtils.DM);
            }else if(databaseType.equalsIgnoreCase(JdbcUtils.SQL_SERVER)){
                sourceString = SQLUtils.format(sourceString,JdbcUtils.SQL_SERVER);
            }else if(databaseType.equalsIgnoreCase(JdbcUtils.POSTGRESQL)){
                sourceString = SQLUtils.format(sourceString,JdbcUtils.POSTGRESQL);
            }else{
                sourceString = SQLUtils.format(sourceString,JdbcUtils.ORACLE);
            }
            resDTO.setSqlStr(sourceString);
        }
        
        return successDATA(resDTO);
    }
}